package org.keshe.user;

import org.keshe.CurrentUser;
import org.keshe.data.StuData;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class NormalUser extends User{

    protected NormalUser(String userName) {
        super(userName);
    }

    @Override
    public boolean changePasswd(String oldVal, String newVal) throws SQLException {
        // 检查旧密码
        String sql = "select * from Master where m_id = ? and passwd = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, this.userName);
        preparedStatement.setString(2, oldVal);
        ResultSet resultSet = preparedStatement.executeQuery();
        if(!resultSet.next())
            return false;
        preparedStatement.close();
        // 设置新密码
        sql = "update Master set passwd = ? where m_id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, newVal);
        preparedStatement.setString(2, this.userName);
        try{
            preparedStatement.execute();
            conn.commit();
        }catch (SQLException e) {
            conn.rollback();
            throw e;
        }finally {
            preparedStatement.close();
        }
        return true;
    }

    /**
     * 登录
     * @param userName 用户名
     * @param passWd 密码
     * @exception SQLException 数据库连接异常
     * @exception NullPointerException conn未初始化
     * @return 若成功登录返回实例，否则返回NULL
     * */
    public static NormalUser logOn(String userName, String passWd) throws SQLException, NullPointerException {
        String sql = "select * from Master where m_id = ? and passwd = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, userName);
        preparedStatement.setString(2, passWd);
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            preparedStatement.close();
            return new NormalUser(userName);
        }
        preparedStatement.close();
        return null;
    }
    /**
     * 获取该宿管管理楼栋的学生信息
     * @return 学生信息
     * */
    public List<StuData> getThisBuildingStuData() throws SQLException {
        String sql = "select Student.*, BedName\n" +
                "from Student\n" +
                "inner join Studentbed on Student.s_id = Studentbed.s_id\n" +
                "inner join BedNameTrans on Studentbed.bed_id = BedNameTrans.bed_id\n" +
                "inner join MasterBuilding MB on BedNameTrans.b_id = MB.b_id\n" +
                "inner join Master M on MB.m_id = M.m_id\n" +
                "where M.m_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, this.getUserName());
        return queryStuData(preparedStatement);
    }
    /**
     * 查找某天未归的学生
     * @param localDate 日期
     * @return 学生信息
     * */
    public List<StuData> getNotReturnStu(LocalDate localDate) throws SQLException {
        String sql = "select Student.*, BedName from\n" +
                "(select * from StudentBuilding\n" +
                " where behaviour = 'OUT'\n" +
                "and time like concat(?, '%')\n" +
                "and (s_id, b_id, time) not in (\n" +
                "select SB1.s_id, SB1.b_id, SB1.time from\n" +
                "StudentBuilding as SB1 inner join StudentBuilding as SB2\n" +
                "on SB1.time < SB2.time and SB1.s_id = SB2.s_id\n" +
                "where SB1.behaviour = 'OUT' and SB2.behaviour = 'IN'\n" +
                "and SB1.time like concat(?, '%') and SB2.time like concat(?, '%'))) as abnBvr\n" +
                "inner join Student on abnBvr.s_id = Student.s_id\n" +
                "inner join MasterBuilding on abnBvr.b_id = MasterBuilding.b_id\n" +
                "inner join Studentbed S on Student.s_id = S.s_id\n" +
                "inner join BedNameTrans BNT on S.bed_id = BNT.bed_id\n" +
                "where MasterBuilding.m_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, localDate.toString());
        preparedStatement.setString(2, localDate.toString());
        preparedStatement.setString(3, localDate.toString());
        preparedStatement.setString(4, this.getUserName());
        return queryStuData(preparedStatement);
    }

    public List<StuData> getNoInOutStu(LocalDate localDate) throws SQLException{
        String sql = "select Student.*, BedName\n" +
                "from Student\n" +
                "inner join Studentbed on Student.s_id = Studentbed.s_id\n" +
                "inner join BedNameTrans on Studentbed.bed_id = BedNameTrans.bed_id\n" +
                "inner join MasterBuilding MB on BedNameTrans.b_id = MB.b_id\n" +
                "inner join Master M on MB.m_id = M.m_id\n" +
                "where Student.s_id not in (select s_id\n" +
                "from StudentBuilding where time like concat(?, '%')) and M.m_id = ?;";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, localDate.toString());
        preparedStatement.setString(2, this.getUserName());
        return queryStuData(preparedStatement);
    }



}
